-- created by execute
-- on 2022/11/4 17:47
-- DESC
insert overwrite table jms_dwd.dwd_sqs_workorder_report_exclude_detail_dt partition (dt)
select
id
,waybill_no
,work_order_no
,work_order_channel
,exclude_metric_item
,first_type_code
,first_type_name
,second_type_code
,second_type_name
,problem_type_id
,problem_type_name
,duty_network_code
,duty_network_name
,transfer_network_code
,transfer_network_name
,accept_network_code
,accept_network_name
,accept_proxy_code
,accept_proxy_name
,accept_virtual_proxy_code
,accept_virtual_proxy_name
,registration_time
,exclude_date
,is_enable
,create_network_code
,create_network_name
,create_time
,create_by_code
,create_by_name
,update_time
,update_by_code
,update_by_name
,cast(to_date(create_time) as string) as dt
from (select *, row_number() over (partition by waybill_no order by update_time desc) as row_rank
      from jms_ods.sqs_workorder_report_exclude_detail
      where dt > date_add('{{ execution_date | cst_ds }}', -60) and dt <= '{{ execution_date | cst_ds }}'
        and to_date(create_time) > date_add('{{ execution_date | cst_ds }}', -60)
        and to_date(create_time) <= '{{ execution_date | cst_ds }}'
     ) t
where row_rank = 1
    distribute by cast(to_date(create_time) as string) , 1;